package com.ikangtai.shecare.common.db;


import android.content.Context;
import android.database.DatabaseErrorHandler;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import com.ikangtai.shecare.common.util.AppLog;

public class DatabaseHelper extends SQLiteOpenHelper {

    // 数据库版本号
    private static final int DATABASE_VERSION = 4;
    // 数据库名
    private static final String DATABASE_NAME = "shecare.db";

    private static DatabaseHelper mInstance;

    // 数据表名

    //用户相关的配置信息，生理信息等
    public static final String USER_PREFERENCE_TABLE_NAME = "user_preference";

    public static final String CREATE_USER_PREFERENCE_TABLE_V2 = "CREATE TABLE IF NOT EXISTS " + USER_PREFERENCE_TABLE_NAME +
            "(userName TEXT NOT NULL PRIMARY KEY, " +
            "userPswd TEXT, " +
            "nickName TEXT," +
            "isNickNameSynced NUMERIC," +
            "state NUMERIC," +
            "isStateSynced NUMERIC," +
            "birthday TEXT," +
            "macAddress TEXT," +
            "lastConnectTime TEXT," +
            "periodLen TEXT," +
            "mensesLen TEXT," +
            "mensesType INTEGER," +
            "lastPeriodDate TEXT," +
            "isHealthInfoSynced NUMERIC," +
            "taskIDs TEXT," +
            "isTaskIDSynced NUMERIC," +
            "isDataDownloaded NUMERIC," +
            "isMACAddressSynced NUMERIC," +
            "UNIQUE(userName))";

    public static final String CREATE_USER_PREFERENCE_TABLE_V4 = "CREATE TABLE IF NOT EXISTS " + USER_PREFERENCE_TABLE_NAME +
            "(userName TEXT NOT NULL PRIMARY KEY, " +
            "userPswd TEXT, " +
            "nickName TEXT," +
            "isNickNameSynced NUMERIC," +
            "state NUMERIC," +
            "isStateSynced NUMERIC," +
            "birthday TEXT," +
            "macAddress TEXT," +
            "isMACAddressSynced NUMERIC," +
            "lastConnectTime TEXT," +
            "periodLen TEXT," +
            "mensesLen TEXT," +
            "mensesType INTEGER," +
            "lastPeriodDate TEXT," +
            "isHealthInfoSynced NUMERIC," +
            "taskIDs TEXT," +
            "isTaskIDSynced NUMERIC," +
            "isDataDownloaded NUMERIC," +
            "openID TEXT, " +
            "UNIQUE(userName))";




    public static final String COLLECTION_TABLE_NAME = "collected_article";
    //算法相关
    public static final String AL_USERDATA_TABLE_NAME = "al_userData";
    public static final String AL_DAYOUTPUT_TABLE_NAME = "al_dayOutput";
    public static final String AL_CYCLEOUTPUT_TABLE_NAME = "al_cycleOutput";
    //温度相关
    public static final String TEMPERATURE_TABLE_NAME = "temperature_table";
    //任务
    public static final String SYS_TASK_TABLE_NAME = "sys_task_table";
    public static final String COMPLETE_TASK_TABLE_NAME = "complete_task_table";
    //基本记录信息
    public static final String RECORD_INFORMATION_TABLE_NAME = "record_info_table";
    //LH试纸
    public static final String RECORD_LH_TABLE_NAME = "record_lh_table";
    //HCG试纸
    public static final String RECORD_HCG_TABLE_NAME = "record_hcg_table";
    //B试纸
    public static final String RECORD_B_TABLE_NAME = "record_b_table";

    public DatabaseHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {
        super(context, name, factory, version);
    }

    public DatabaseHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version, DatabaseErrorHandler errorHandler) {
        super(context, name, factory, version, errorHandler);
    }

    public DatabaseHelper(Context context) {
        // CursorFactory设置为null,使用系统默认的工厂类
        super(context.getApplicationContext(), DATABASE_NAME, null, DATABASE_VERSION);
        // 数据库实际被创建是在getWritableDatabase()或getReadableDatabase()方法调用时
        AppLog.i("hxiao DatabaseHelper Constructor");
    }

    public synchronized static DatabaseHelper getInstance(Context context) {

        if (mInstance == null) {
            synchronized (DatabaseHelper.class) {
                if (mInstance == null)
                    mInstance = new DatabaseHelper(context);
            }
        }

        return mInstance;
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        // 调用时间：数据库第一次创建时onCreate()方法会被调用

        // onCreate方法有一个 SQLiteDatabase对象作为参数，根据需要对这个对象填充表和初始化数据
        // 这个方法中主要完成创建数据库后对数据库的操作

        AppLog.i("DatabaseHelper onCreate");

        // 执行创建表的SQL语句
        // 即便程序修改重新运行，只要数据库已经创建过，就不会再进入这个onCreate方法
        try {
            db.beginTransaction();

            // replace into 根据UNIQUE约束的字段，确定被替换的是哪一条记录。如果不存在要替换的记录， 那么就会插入一条新的记录。
            db.execSQL(CREATE_USER_PREFERENCE_TABLE_V4);

            db.execSQL("CREATE TABLE IF NOT EXISTS " + SYS_TASK_TABLE_NAME +
                    "(sysTaskID INTEGER NOT NULL," +
                    "userName TEXT NOT NULL," +
                    "isDelete INTEGER," +
                    "isChoose INTEGER," +
                    "taskDetail TEXT," +
                    "taskDetailEn," +
                    "glossary TEXT," +
                    "glossaryEn TEXT," +
                    "glossaryColor TEXT," +
                    "bgColor TEXT," +
                    "glossaryBegin INTEGER," +
                    "glossaryEnd INTEGER," +
                    "glossaryBeginEn INTEGER," +
                    "glossaryEndEn INTEGER," +
                    "date TEXT," +
                    "task TEXT," +
                    "taskEn TEXT," +
                    "UNIQUE(sysTaskID, userName))");


            db.execSQL("CREATE TABLE IF NOT EXISTS " + COLLECTION_TABLE_NAME +
                    "(collectionID TEXT NOT NULL PRIMARY KEY, " +
                    "articleTitle TEXT, " +
                    "articleId INTEGER," +
                    "collectionFrom NUMERIC," +
                    "collectionDate TEXT," +
                    "collectionDelete NUMERIC," +
                    "userName TEXT," +
                    "isSynced NUMERIC, UNIQUE(userName, articleId))");

            db.execSQL("CREATE TABLE IF NOT EXISTS " + AL_USERDATA_TABLE_NAME +
                    "(userName TEXT NOT NULL PRIMARY KEY, " +
                    "validCycleCount INTEGER," +
                    "avgMensesLen FLOAT," +
                    "mensesLenError FLOAT," +
                    "avgCycleLen FLOAT," +
                    "cycleLenError FLOAT," +
                    "avgLuteumLen FLOAT," +
                    "avgOvulationDay FLOAT," +
                    "abCaseAlert INTEGER, " +
                    "isSynced NUMERIC, UNIQUE(userName))");

            db.execSQL("CREATE TABLE IF NOT EXISTS " + AL_DAYOUTPUT_TABLE_NAME +
                    "(userName TEXT NOT NULL, " +
                    "periodAchieveForecast INTEGER," +
                    "periodAchieveConfirm INTEGER," +
                    "periodAvoidForecast INTEGER," +
                    "periodAvoidConfirm INTEGER," +
                    "homePageMensesEnd INTEGER," +
                    "homePageOvulation INTEGER," +
                    "homePageConceptChance FLOAT," +
                    "homePageNextMenses INTEGER," +
                    "dateStr TEXT," +
                    "dayOfCycle INTEGER, " +
                    "isSynced NUMERIC, UNIQUE(userName, dateStr))");

            db.execSQL("CREATE TABLE IF NOT EXISTS " + AL_CYCLEOUTPUT_TABLE_NAME +
                    "(userName TEXT NOT NULL, " +
                    "menstruationStartForecast TEXT," +
                    "menstruationEndForecast TEXT," +
                    "menstruationStartConfirm TEXT," +
                    "menstruationEndConfirm TEXT," +
                    "BBTRiseDay TEXT," +
                    "peakDay TEXT," +
                    "ovulationDayForecast TEXT," +
                    "ovulationDayConfirm TEXT," +
                    "ovulationDayUserRecord TEXT," +
                    "ovulationDayBBTRise TEXT," +
                    "ovulationDayNextMenstruation TEXT," +
                    "fertileWindowStartForecast TEXT," +
                    "fertileWindowEndForecast TEXT," +
                    "fertileWindowStartConfirm TEXT," +
                    "fertileWindowEndConfirm TEXT," +
                    "dangerWindowStartForecast TEXT," +
                    "dangerWindowEndForecast TEXT," +
                    "dangerWindowStartConfirm TEXT," +
                    "dangerWindowEndConfirm TEXT," +
                    "nextMenstruationStartForecast TEXT," +
                    "nextMenstruationEndForecast TEXT," +
                    "nextOvulationDayForecast TEXT," +
                    "nextFertileWindowStartForecast TEXT," +
                    "nextFertileWindowEndForecast TEXT," +
                    "nextDangerWindowStartForecast TEXT," +
                    "nextDangerWindowEndForecast TEXT," +
                    "cycleNumber INTEGER," +
                    "ovulationDayUserRecordBasis INTEGER, " +
                    "isSynced NUMERIC, UNIQUE(userName, cycleNumber))");

            db.execSQL("CREATE TABLE IF NOT EXISTS " + TEMPERATURE_TABLE_NAME +
                    "(temperatureId TEXT NOT NULL PRIMARY KEY, " +
                    "userName TEXT, " +
                    "measureTime LONG," +
                    "editTime LONG," +
                    "temperature TEXT," +
                    "isBBT INTEGER," +
                    "type INTEGER," +
                    "isSynced INTEGER," +
                    "isDelete INTEGER, UNIQUE(userName, temperatureId))");

            db.execSQL("CREATE TABLE IF NOT EXISTS " + COMPLETE_TASK_TABLE_NAME +
                    "(userName TEXT NOT NULL, " +
                    "date TEXT NOT NULL, " +
                    "taskID INTEGER," +
                    "isDelete INTEGER," +
                    "isSynced INTEGER, UNIQUE(userName, date, taskID))");

            db.execSQL("CREATE TABLE IF NOT EXISTS " + RECORD_INFORMATION_TABLE_NAME +
                    "(id TEXT NOT NULL PRIMARY KEY, " +
                    "userName TEXT, " +
                    "recordDate TEXT, " +
                    "recordEditDate TEXT," +
                    "symptomInfo INTEGER," +
                    "mucusInfo INTEGER," +
                    "mensesInfo INTEGER," +
                    "copulationInfo INTEGER," +
                    "ovulationInfo INTEGER," +
                    "sexTime TEXT, " +
                    "memeoInfo TEXT, " +
                    "isSynced INTEGER," +
                    "LHResult INTEGER," +
                    "HCGResult INTEGER," +
                    "isOvulationDay INTEGER," +
                    " UNIQUE(userName, id))");

            db.execSQL("CREATE TABLE IF NOT EXISTS " + RECORD_LH_TABLE_NAME +
                    "(userName TEXT NOT NULL, " +
                    "date TEXT NOT NULL, " +
                    "LHPaperID TEXT," +
                    "result INTEGER," +
                    "isDelete INTEGER," +
                    "isSynced INTEGER," +
                    "UNIQUE(userName, LHPaperID)" +
                    ")");

            db.execSQL("CREATE TABLE IF NOT EXISTS " + RECORD_HCG_TABLE_NAME +
                    "(userName TEXT NOT NULL, " +
                    "date TEXT NOT NULL, " +
                    "HCGPaperID TEXT," +
                    "result INTEGER," +
                    "isDelete INTEGER," +
                    "isSynced INTEGER," +
                    "UNIQUE(userName, HCGPaperID))");

            db.execSQL("CREATE TABLE IF NOT EXISTS " + RECORD_B_TABLE_NAME +
                    "(userName TEXT NOT NULL, " +
                    "date TEXT NOT NULL, " +
                    "BUID TEXT," +
                    "isDelete INTEGER," +
                    "isSynced INTEGER," +
                    "UNIQUE(userName, date))");

            db.setTransactionSuccessful();
        } catch (Exception e) {
            // TODO: handle exception
            AppLog.i("db Transaction Exception! e = " + e.getMessage());
        } finally {
            db.endTransaction();
        }
    }

    // 在getWritableDatabase()或getReadableDatabase()方法调用时，
    // 检查现有db的oldVersion(在之前构造函数的时候存储了)
    // 和现在构造函数里的newVersion相比, 不同则调用
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        AppLog.i("DatabaseHelper onUpgrade! oldVersion = " + oldVersion + ", newVersion = " + newVersion);

        if (oldVersion < newVersion) {
            for (int i = oldVersion + 1; i <= newVersion; i++) {
                //TODO upgrade
                switch (i) {
                    case 2:
                        // 此版本为USER_PREFERENCE_TABLE_NAME 表的 MAC address 增加了一个sync字段
                        alterTable(db, USER_PREFERENCE_TABLE_NAME, CREATE_USER_PREFERENCE_TABLE_V2);
                        break;

                    case 3:
                        // 此版本为USER_PREFERENCE_TABLE_NAME 表的 MAC address 增加了一个sync字段
                        try {
                            db.beginTransaction();

                            db.execSQL("CREATE TABLE IF NOT EXISTS " + SYS_TASK_TABLE_NAME +
                                    "(sysTaskID INTEGER NOT NULL," +
                                    "userName TEXT NOT NULL," +
                                    "isDelete INTEGER," +
                                    "isChoose INTEGER," +
                                    "taskDetail TEXT," +
                                    "taskDetailEn," +
                                    "glossary TEXT," +
                                    "glossaryEn TEXT," +
                                    "glossaryColor TEXT," +
                                    "bgColor TEXT," +
                                    "glossaryBegin INTEGER," +
                                    "glossaryEnd INTEGER," +
                                    "glossaryBeginEn INTEGER," +
                                    "glossaryEndEn INTEGER," +
                                    "date TEXT," +
                                    "task TEXT," +
                                    "taskEn TEXT," +
                                    "UNIQUE(sysTaskID, userName))");
                            db.setTransactionSuccessful();
                        } catch (Exception e) {
                            // TODO: handle exception
                            AppLog.i("db Transaction Exception! e = " + e.getMessage());
                        } finally {
                            db.endTransaction();
                        }

                        break;

                    case 4:
                        alterTable(db, USER_PREFERENCE_TABLE_NAME, CREATE_USER_PREFERENCE_TABLE_V4);
                        break;

                    default:
                        break;
                }// end switch
            }// end for
        }// end if
    }

    /**
     * 注意：此方法只适合增加一个列！减少或者增加多于一个列不适用！
     * @param db
     * @param tableName
     * @param newTableCreateSQLStr
     */
    private void alterTable(SQLiteDatabase db, String tableName, String newTableCreateSQLStr){
        try {
            db.beginTransaction();

            String TEMP_TABLE_NAME = "_temp_table";
            db.execSQL("ALTER TABLE " + tableName + " rename to " + TEMP_TABLE_NAME);
            db.execSQL(newTableCreateSQLStr);
            db.execSQL("INSERT INTO " + tableName + " SELECT *, '' FROM " + TEMP_TABLE_NAME);
            db.execSQL("DROP TABLE " + TEMP_TABLE_NAME);

            db.setTransactionSuccessful();
        } catch (Exception e) {
            // TODO: handle exception
            AppLog.i("db Transaction Exception! e = " + e.getMessage());
        } finally {
            db.endTransaction();
        }
    }
}
